package com.freeter.ExcelUtil;

import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * xls 导出 工具 20150507
 * 
 * @author zhou
 *
 */
public class XlsExportTool {

	/**
	 * 
	 * @Title: doExport
	 * @Description: 生成sheet页面 数据
	 * @param @param
	 *            title {"报警类型","呼号","设备组","经度","纬度","报警时间"}
	 * @param @param
	 *            col_width {5000,5000,7000,7000,7000,7000}
	 * @param @param
	 *            dataList 数据源 [{"name":"周","age":10},{"name":"wang","age":21}]
	 * @param @param
	 *            data_key map的key
	 * @param @param
	 *            sheet 设定文件
	 * @return void 返回类型
	 * @throws @author
	 *             zhou
	 * @date 2015年4月7日 下午3:14:44
	 */
	public static void doExport(String[] title, int[] col_width, List<Map<String, Object>> dataList, String[] data_key,
			HSSFSheet sheet) {
		HSSFCellStyle title_style = getTitleStyle(sheet);// 获取列头样式对象
		HSSFCellStyle cel_style = getCellStyle(sheet); // 单元格样式对象

		int row_indx = 0;

		// 生成标题行
		setTitle(title, col_width, sheet, title_style, row_indx);

		// 首行冻结
		sheet.createFreezePane(0, 1);

		// 正文
		if (dataList == null) {
			return;
		}
		row_indx++;

		// 生成正文
		row_indx = doSetContent(dataList, data_key, sheet, cel_style, row_indx);

	}

	public static void doExport(boolean isAddFristBlankLine, String[] title, int[] col_width,
			List<Map<String, Object>> dataList, String[] data_key, HSSFSheet sheet) {
		HSSFCellStyle title_style = getTitleStyle(sheet);// 获取列头样式对象
		HSSFCellStyle cel_style = getCellStyle(sheet); // 单元格样式对象

		int row_indx = 0;
		if (isAddFristBlankLine) {
			setFristBlank(title, col_width, sheet, title_style, row_indx);
			row_indx++;
		}

		// 生成标题行
		setTitle(title, col_width, sheet, title_style, row_indx);

		// 首行冻结
		sheet.createFreezePane(0, 2);

		// 正文
		if (dataList == null) {
			return;
		}
		row_indx++;
		// 生成正文
		row_indx = doSetContent(dataList, data_key, sheet, cel_style, row_indx);
	}

	private static int doSetContent(List<Map<String, Object>> dataList, String[] data_key, HSSFSheet sheet,
			HSSFCellStyle cel_style, int row_indx) {
		for (Map<String, Object> map : dataList) {

			// 创建行(从下面的i+1要注意,第0行是列头,因此创建新行要从下一行开始)
			HSSFRow row = sheet.createRow(row_indx); // 创建所需的行数

			for (int col = 0; col < data_key.length; col++) {

				HSSFCell cell = row.createCell(col, CellType.STRING);
				//HSSFCell cell = row.createCell(col, HSSFCell.CELL_TYPE_STRING);// 设置单元格的数据类型
				cell.setCellValue(map.get(data_key[col]) == null ? "" : map.get(data_key[col]).toString());
				cell.setCellStyle(cel_style);
			}

			row_indx++;
		}
		return row_indx;
	}

	private static void setFristBlank(String[] title, int[] col_width, HSSFSheet sheet, HSSFCellStyle title_style,
			int row_indx) {
		// 设置标题
		HSSFRow row1 = sheet.createRow(row_indx); // 在索引0的位置创建行(最顶端的行)
		HSSFCell cell1 = null; // 在索引0的位置创建单元格(左上端)

		// 定义所需列数
		int col_zise = title.length;

		for (int n = 0; n < col_zise; n++) {
			cell1 = row1.createCell(n); // 创建列头对应个数的单元格
			//cell1.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
			cell1.setCellType(CellType.STRING);
			cell1.setCellValue(""); // 设置列头单元格的值
			cell1.setCellStyle(title_style); // 设置列头单元格样式
			sheet.setColumnWidth(n, col_width[n]);
		}
	}

	private static void setTitle(String[] title, int[] col_width, HSSFSheet sheet, HSSFCellStyle title_style,
			int row_indx) {
		// 设置标题
		HSSFRow row1 = sheet.createRow(row_indx); // 在索引0的位置创建行(最顶端的行)
		HSSFCell cell1 = null; // 在索引0的位置创建单元格(左上端)

		// 定义所需列数
		int col_zise = title.length;

		for (int n = 0; n < col_zise; n++) {
			cell1 = row1.createCell(n); // 创建列头对应个数的单元格
			cell1.setCellType(CellType.STRING);
			//cell1.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
			cell1.setCellValue(title[n]); // 设置列头单元格的值
			cell1.setCellStyle(title_style); // 设置列头单元格样式
			sheet.setColumnWidth(n, col_width[n]);
		}
	}

	/*
	 * 列头单元格样式
	 */
	private static HSSFCellStyle getTitleStyle(HSSFSheet sheet) {

		// 设置字体
		HSSFFont font = sheet.getWorkbook().createFont();
		// 设置字体大小
		font.setFontHeightInPoints((short) 11);
		// 字体加粗
		//font.setBoldweight(HSSFFont.);
		// 设置字体名字
		// font.setFontName("Courier New");
		// 设置样式;
		HSSFCellStyle style = sheet.getWorkbook().createCellStyle();
		// 设置底边框;
		style.setBorderBottom(BorderStyle.THIN);
		// 设置底边框颜色;
		// style.setBottomBorderColor(HSSFColor.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(BorderStyle.THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
		// 设置右边框;
		style.setBorderRight(BorderStyle.THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
		// 设置顶边框;
		style.setBorderTop(BorderStyle.THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HorizontalAlignment.CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(VerticalAlignment.CENTER);

		return style;

	}

	/*
	 * 列数据信息单元格样式
	 */
	private static HSSFCellStyle getCellStyle(HSSFSheet sheet) {
		// 设置字体
		HSSFFont font = sheet.getWorkbook().createFont();
		// 设置字体大小
		 font.setFontHeightInPoints((short)10);
		// 字体加粗
		 font.setBold(true);
		// 设置字体名字
		font.setFontName("Courier New");
		// 设置样式;
		HSSFCellStyle style = sheet.getWorkbook().createCellStyle();
		// 设置底边框;
		style.setBorderBottom(BorderStyle.THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
		// 设置左边框;
		style.setBorderLeft(BorderStyle.THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());
		// 设置右边框;
		style.setBorderRight(BorderStyle.THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
		// 设置顶边框;
		style.setBorderTop(BorderStyle.THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HorizontalAlignment.CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(VerticalAlignment.CENTER);

		return style;

	}
}
